package main

import (
	"fmt"
	"github.com/xuri/excelize/v2"
)

func main() {
	f := excelize.NewFile()

	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	cols := []string{
		fmt.Sprintf("A1:A%d", excelize.TotalRows),
		fmt.Sprintf("B1:B%d", excelize.TotalRows),
		fmt.Sprintf("C1:C%d", excelize.TotalRows),
	}
	validations := [][]string{
		[]string{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", " "},
		[]string{"A", "B", "C", "D", "E", "F", "G"},
		[]string{"一", "二", "三", "四", "五", "六", "七", "八", "九", "十", " "},
	}
	sheetName := f.GetSheetName(f.GetActiveSheetIndex())
	err := MakeValidate(cols, validations, f, sheetName)
	if err != nil {
		fmt.Println(err)
		return
	}
	// 保存文件
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

func MakeValidate(cols []string, validations [][]string, f *excelize.File, sheetName string) error {
	sqrefs, err := createValidateData(validations, f)
	if err != nil {
		return err
	}
	fmt.Println("sqrefs:", sqrefs)
	dv := excelize.NewDataValidation(true)
	for index := range cols {
		dv.SetSqref(cols[index])
		//err := dv.SetDropList(validations[index])
		dv.SetSqrefDropList(sqrefs[index])
		err = f.AddDataValidation(sheetName, dv)
		if err != nil {
			return err
		}
	}
	return nil
}

func createValidateData(validations [][]string, f *excelize.File) (datas []string, err error) {
	var (
		sheetName      = "DATA"
		dataSheetIndex int
	)
	dataSheetIndex, err = f.NewSheet(sheetName)
	dataSheetName := f.GetSheetName(dataSheetIndex)
	if err != nil {
		return
	}
	for i := range validations {
		var (
			cellStart, cellEnd string
		)
		cellStart, err = excelize.CoordinatesToCellName(1, i+1, true)
		if err != nil {
			return
		}
		cellEnd, err = excelize.CoordinatesToCellName(len(validations[i])+1, i+1, true)
		if err != nil {
			return
		}
		err = f.SetSheetRow(dataSheetName, cellStart, &validations[i])
		if err != nil {
			return
		}
		datas = append(datas, fmt.Sprintf("%s!%s:%s", sheetName, cellStart, cellEnd))
	}
	err = f.SetSheetVisible(sheetName, false, false)
	return
}
